package com.x.reader.dao;

import com.x.framework.Base;
import com.x.framework.dao.BaseDao;
import com.x.reader.model.Audio;
import com.x.reader.model.AudioChapter;
import com.x.reader.model.User;
import org.springframework.stereotype.Repository;

import java.util.ArrayList;
import java.util.List;

@Repository
public class AudioDao extends BaseDao {

    private final static String selectAudioListSql = "select t1.* from reader_audio t1, reader_category t2 where t1.category_id = t2.category_id ";

    public List<Audio> selectAudioList(Audio audio) throws Exception {
        StringBuilder sb = new StringBuilder(selectAudioListSql);
        List params = new ArrayList();
        if (Base.isNotNull(audio.getAudioId())) {
            sb.append("and t1.audio_id = ? ");
            params.add(audio.getAudioId());
        }
        if (Base.isNotNull(audio.getCategoryId())) {
            sb.append("and t1.category_id = ? ");
            params.add(audio.getCategoryId());
        }
        if (Base.isNotNull(audio.getAudioTitle())) {
            sb.append("and (t1.audio_title like ? or t1.audio_author like ?) ");
            params.add("%" + audio.getAudioTitle() + "%");
            params.add("%" + audio.getAudioTitle() + "%");
        }
        sb.append("order by t1.audio_create_time desc");
        return this.queryForList(sb.toString(), Audio.class, params, audio);
    }


    private final static String selectAudioNumSql = "select count(1) as num from reader_audio t1, reader_category t2 where t1.category_id = t2.category_id ";

    public int selectAudioNum(Audio audio) throws Exception {
        StringBuilder sb = new StringBuilder(selectAudioNumSql);
        List params = new ArrayList();
        if (Base.isNotNull(audio.getAudioId())) {
            sb.append("and t1.AUDIO_ID = ? ");
            params.add(audio.getAudioId());
        }
        if (Base.isNotNull(audio.getCategoryId())) {
            sb.append("and t1.CATEGORY_ID = ? ");
            params.add(audio.getCategoryId());
        }
        if (Base.isNotNull(audio.getAudioTitle())) {
            sb.append("and (t1.audio_title like ? or t1.audio_author like ?) ");
            params.add("%" + audio.getAudioTitle() + "%");
            params.add("%" + audio.getAudioTitle() + "%");
        }
        return this.queryForInt(sb.toString(), params);
    }

    private final static String selectAudioChapterListSql = "select t1.audio_chapter_id, " +
            "(t2.audio_title || ' ' || t1.audio_chapter_title) as audio_chapter_title, " +
            "t1.audio_chapter_cover, " +
            "t1.audio_chapter_file, " +
            "t1.audio_chapter_times, " +
            "t1.audio_chapter_create_time, " +
            "t1.audio_id " +
            "from reader_audio_chapter t1, reader_audio t2 " +
            "where t1.audio_id = t2.audio_id ";

    public List<AudioChapter> selectAudioChapterList(AudioChapter audioChapter) throws Exception {
        StringBuilder sb = new StringBuilder(selectAudioChapterListSql);
        List params = new ArrayList();
        if (Base.isNotNull(audioChapter.getAudioId())) {
            sb.append("and t1.audio_id = ? ");
            params.add(audioChapter.getAudioId());
        }
        if (Base.isNotNull(audioChapter.getAudioChapterId())) {
            sb.append("and t1.audio_chapter_id = ? ");
            params.add(audioChapter.getAudioChapterId());
        }
        sb.append("order by audio_chapter_id");
        return this.queryForList(sb.toString(), AudioChapter.class, params, audioChapter);
    }

}
